package main;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

public class MyExecute {
	private static final Logger LOGGER = Logger.getLogger(MyExecute.class);

	private final String INS_PRE = "<PREFIX>";
	private final String INS_TBN = "<TableName>";
	private final String INS_NEW = "<INS_NEW>";
	private final String INS_OLD = "<INS_OLD>";
	private final String INS_CLN = "<INS_COLUMNS_NAME>";
	private final String COLUMN_NAME = "COLUMN_NAME";
	private Statement stmt;
	private Connection localConnection;

	public MyExecute() {
		LOGGER.info("Initialize " + getClass().getCanonicalName());
		this.doConnectToLocal();
	}

	private boolean doConnectToLocal() {
		try {
			localConnection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.188:1521:rmsdb", "rms13dev", "btmgcs");
		}catch (SQLException e) {
			LOGGER.error(e.getMessage(), e.getCause());
		}

		if(localConnection == null) {
			return false;
		}

		return true;
	}

	public void myExecuteSQL(Connection connection) {
		if(connection == null) {
			LOGGER.info("There is no connection: " + connection);
			return;
		}
		if(MyGenerater.LISTTABLENAMES == null) {
			LOGGER.info("There is no list of table names");
			return;
		}

		final String COMMA = ", ";
		final String oldPseudo = ":old.";
		final String newPseudo = ":new.";

		String columnName = null;
		StringBuilder sbOld = new StringBuilder();
		StringBuilder sbNew = new StringBuilder();
		StringBuilder sbColumnName = new StringBuilder();

		String tmpLine = null;
		try {
			stmt = connection.createStatement();

			FileWriter fw = new FileWriter(new File("D:/manh.vu/Workspace/CreateTrigger/logs/OutputFile.sql"), true);
			for(String tableName : MyGenerater.LISTTABLENAMES) {
				ResultSet rs = stmt.executeQuery(getSQLStr(tableName));
				sbNew.delete(0, sbNew.length());
				sbOld.delete(0, sbOld.length());
				sbColumnName.delete(0, sbColumnName.length());
				while (rs.next()) {
					columnName = rs.getString(COLUMN_NAME);
					sbOld.append(COMMA);
					sbNew.append(COMMA);

					sbOld.append(oldPseudo);
					sbOld.append(columnName);

					sbNew.append(newPseudo);
					sbNew.append(columnName);

					sbColumnName.append(COMMA);
					sbColumnName.append(columnName);
				}

				BufferedReader brTemplate = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("/resources/template.sql")));

				while ((tmpLine = brTemplate.readLine()) != null) {
					if(tmpLine.contains(INS_PRE)) {
						tmpLine = tmpLine.replace(INS_PRE, MyGenerater.PREFIX);
					}

					if(tmpLine.contains(INS_TBN)) {
						tmpLine = tmpLine.replace(INS_TBN, tableName);
					}

					if(tmpLine.contains(INS_OLD)) {
						tmpLine = tmpLine.replace(INS_OLD, sbOld.toString());
					}

					if(tmpLine.contains(INS_NEW)) {
						tmpLine = tmpLine.replace(INS_NEW, sbNew.toString());
					}

					if(tmpLine.contains(INS_CLN)) {
						tmpLine = tmpLine.replace(INS_CLN, sbColumnName.toString());
					}

					fw.write(tmpLine);
					fw.write('\n');
				}
				brTemplate.close();
			}
			fw.close();

			stmt.close();
		}catch (SQLException | IOException e) {
			LOGGER.error(e.getMessage(), e.getCause());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Object#finalize()
	 */
	@Override
	protected void finalize() throws Throwable {
		if(stmt != null && !stmt.isClosed()) {
			stmt.close();
		}

		if(this.localConnection != null) {
			this.localConnection.close();
		}

		super.finalize();
	}

	/**
	 * @return the localConnection
	 */
	public Connection getLocalConnection() {
		return localConnection;
	}

	public String getSQLStr(String tableName) {
		String bfStr = null;

		BufferedReader br = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("/resources/sql.sql")));
		try {
			bfStr = br.readLine();
			bfStr = bfStr.replace(INS_TBN, tableName);
			bfStr = bfStr.substring(0, bfStr.length() - 1);
			br.close();
		}catch (IOException e) {
			LOGGER.error(e.getMessage(), e.getCause());
		}

		LOGGER.info("\n" + bfStr);

		return bfStr;
	}
}

class PrintColumnTypes {

	public static void printColTypes(ResultSetMetaData rsmd, StringBuilder sb) throws SQLException {
		int columns = rsmd.getColumnCount();
		for(int i = 1; i <= columns; i++) {
			int jdbcType = rsmd.getColumnType(i);
			String name = rsmd.getColumnTypeName(i);
			sb.append("Column " + i + " is JDBC type " + jdbcType);
			sb.append(", which the DBMS calls " + name);
			sb.append('\n');
		}
	}
}
